use model_graph_types::{
    connector::{Connector, ExecuteOptions, QueryOptions},
    database::DataSource,
    extractor::{ExtractFromDatabaseOption, MetadataExtractor, MetadataPackage},
    modeling::{
        ClassAttribute as  Attribute, Class, CollectionType, NameSpace, NameSpaceBuilder, Value, ValueType,
    },
};

use itertools::Itertools;
use mysql_connector::MySqlConnector;

/// 根据数据库获取到元数据
pub async fn extract_from_database(
    data_source: &DataSource,
    options: ExtractFromDatabaseOption,
) -> anyhow::Result<MetadataPackage> {
    let namespace = NameSpaceBuilder::default()
        .identifier(data_source.url.clone())
        .name(data_source.url.clone())
        .build()?;

    //连接数据库
    let connector = MySqlConnector::open(data_source).await?;
    //执行语句获取到元数据
    let sql = String::from(
        r#"select t.table_schema, t.table_name, t.table_type, t.engine,t.table_collation, t.table_comment
        from information_schema.tables t where table_schema = database()"#,
    );

    let cols_sql = String::from(
        r#"select c.table_name, c.ordinal_position, c.column_name,(
            CASE
            WHEN c.column_default is NULL  THEN '' 
            ELSE c.column_default 
            END
        ) AS column_default, c.is_nullable, c.data_type,c.column_type,c.column_key,c.extra  from information_schema.COLUMNS c
            WHERE c.TABLE_SCHEMA = database() and c.table_name = ?  order by c.ordinal_position"#,
    );
    //查询当前库的所有的表
    let (_, result_values) = connector.query(&sql, &vec![], QueryOptions {}).await?;
    tracing::debug!("result:{:?}", result_values);

    let mut classes = vec![];

    for value in result_values {
        //每一个表
        match value {
            Value::Array(cols) => {
                //t.table_schema, t.table_name, t.table_type, t.engine,t.table_collation, t.table_comment
                let mut class = Class::default();

                class.name = cols.get(1).map_or(String::new(), |f| match f {
                    Value::String(sv) => sv.clone(),
                    _ => String::new(),
                });

                class.identifier = cols.get(1).map_or(String::new(), |f| match f {
                    Value::String(sv) => sv.clone(),
                    _ => String::new(),
                });

                class.description =Some( cols.get(5).map_or(String::new(), |f| match f {
                    Value::String(sv) => sv.clone(),
                    _ => String::new(),
                }));
                //所有的属性
                let mut attributes = vec![];
                //cid name, type, notnull, dflt_value, pk
                let attrs = connector
                    .query(
                        &cols_sql,
                        &vec![Value::String(class.identifier.clone())],
                        QueryOptions {},
                    )
                    .await;
                // tracing::debug!("attrs:{:?}", attrs);
                let (_, attrs) = attrs.unwrap();

                for attr_value in attrs {
                    //每一个表
                    match attr_value {
                        Value::Array(cols) => {
                            let mut attribute = Attribute::default();
                            attribute.position = cols.get(1).map_or(0, |f| match f {
                                Value::Number(sv) => sv.clone() as i32,
                                _ => 0,
                            });
                            //
                            attribute.identifier = cols.get(2).map_or(String::new(), |f| match f {
                                Value::String(sv) => sv.clone(),
                                _ => String::new(),
                            });
                            attribute.name = cols.get(2).map_or(String::new(), |f| match f {
                                Value::String(sv) => sv.clone(),
                                _ => String::new(),
                            });
                            attribute.collection_type = CollectionType::Single;
                            attribute.scope = None;

                            let col_type = cols.get(5).map_or(String::new(), |f| match f {
                                Value::String(sv) => sv.clone(),
                                _ => String::new(),
                            });

                            let col_type2 = cols.get(6).map_or(String::new(), |f| match f {
                                Value::String(sv) => sv.clone(),
                                _ => String::new(),
                            });
                            attribute.data_type = _get_attribute_type(col_type.as_str());

                            attribute.is_nullable = cols.get(4).map_or(false, |f| match f {
                                Value::String(sv) => sv.as_str() == "YES",
                                _ => false,
                            });

                            attribute.default_value = cols.get(3).map_or(None, |f| match f {
                                Value::String(sv) => Some(sv.clone()),
                                _ => None,
                            });

                            attribute.is_key = cols.get(7).map_or(false, |f| match f {
                                Value::String(sv) => sv.as_str() == "PRI",
                                _ => false,
                            });

                            attribute.options = Some(
                                serde_json::to_string(&serde_json::json!({
                                "data_type": col_type,
                                "column_type":col_type2
                                }))
                                .map_err(|err| anyhow::anyhow!("{}", err))?,
                            );

                            attributes.push(attribute);
                        }
                        _ => {
                            tracing::debug!("==");
                        }
                    }
                }
                class.attributes = Some(attributes);

                classes.push(class);
            }
            _ => {
                tracing::debug!("==");
            }
        }
    }

    // let package = Package {
    //     namespaces: vec![namespace],
    //     classes: classes,
    //     namespace_classes: vec![],
    //     relations: vec![],
    // };

    Ok(MetadataPackage {
        database: String::from("TODO"),
    })
}

fn _get_attribute_type(str: &str) -> ValueType {
    match str.to_uppercase().as_str() {
        "TIMESTAMP" => ValueType::Timestamp,
        "DATETIME" => ValueType::DateTime,
        "DATE" => ValueType::DateTime,
        "TIME" => ValueType::DateTime,
        "YEAR" => ValueType::Number,
        "VARCHAR" => ValueType::String,
        "CHAR" => ValueType::Char,
        "TEXT" => ValueType::String,
        "LONGTEXT" => ValueType::String,
        "MEDIUMTEXT" => ValueType::String,
        "BIGINT" => ValueType::Number,
        "INT" => ValueType::Number,
        "FLOAT" => ValueType::Float,
        "DOUBLE" => ValueType::Float,
        "TINYINT" => ValueType::Number,
        "SMALLINT" => ValueType::Number,
        "BLOB" => ValueType::Bytes,
        "BOOLEAN" => ValueType::Boolean,
        "BOOL" => ValueType::Boolean,
        _ => {
            tracing::error!("没有对应的数据类型:{}", str);
            ValueType::None
        }
    }
}
